Microsoft Excel is full of all sorts of tools some tools are more widely used more common in practice
and some tools are a little lesser known.
We're going to take a look at some of these lesser known more advanced type tools that we can use within
Microsoft Excel.
The first one that we're going to take a look at here is called the goal seat.
This falls into a category of what if analysis.
If you take a look at my screen here I've got the example file open.
I'm currently on the worksheet tab called Goal Seek.
And if you look at the Data tab top of the screen underneath the data tool section you have what if
analysis.
Mean if I get that button click you'll find a handful of features that get These are some of the lesser
known features that are built inside of Excel but very powerful very robust tools like I mentioned the
first one that I want to discuss here is the goal CG.
So first up before I jump in there start to demonstrate it here's the setup.
So on this goes go seek worksheet.
I've got a simple little table.
It's got a mortgage amount 220000.
It's got an interest rate of 8 percent.
Ouch.
And her term in months for the next 300 months.
What I'd like to know is what my current PAME it's going to be using those arguments those variables
.
Now we're going to figure this out using a function that's built into excel called the payment function
or P and T then we're going to turn around and see how we can adjust that current payment to fit more
of my budget but rather than us getting in there and trying to monkey around with these numbers here
you know when I can't afford that a straight drop in the 220 to 200000 was lower the interest rate you
know see if I get that towards my budget rather than us doing that manually we'll get Excel to do this
for us through that goal seek tool.
Take a look.
So first I'm going to go over to D-3 and I'm going to create a simple little calculation here that will
find out the current payment using these three arguments here.
And this is called the payment function or PM T.
So I'm going to go to my formulas down.
I'm going to go into financial and find P and T.
There it is.
Payment function.
Now the Pinti function takes a handful of arguments from us.
I'm only going to use three of them the three required ones the first one wants to know the rate which
is the 8 percent.
This cell B-3 now at an interest rate as far as a mortgage or car payments on it's an AP are an annual
percentage rate.
So I'm going to divide that by 12.
I'll say B3 divided by 12.
The NPR is the number of payments I made a grab termine month's value which is 300 and the PV is our
present value which is the 220000 sayto Kaylor.
Now I'm going to go in here okay.
And roughly 60 98 almost 70 dollars is what I'm going to be paid monthly for a $220000 home and an 8
percentage for the next three hundred months.
Yeah.
Ouch.
Now right now it's in red and it's in print Siska that's a negative value.
And I could quickly change that just you don't have to do this but if I go to my formula here and just
put a minus sign in front of that P.A. and my anarchy this will convert it into a positive value.
So roughly 60 98 thousand six hundred ninety dollars for the next 300 months.
Now I think you know what's that's a great little function.
You know next time I go buy a house or a car or whatever I'm going to take this with me.
You know I aid but Excel says I could pay this.
Well you know what.
I can't afford that much.
That's sixteen hundred dollars a month.
Wow.
I want to get that down to like eleven hundred dollars.
What can I do to get that down to eleven hundred dollars.
Now I could in the past what I would do is I would manually adjust these numbers so you know what I
can afford 220000 so I have lower that down to 200000.
Let's see if we get twenty three.
That's getting closer but not quite there yet.
You know I don't want to go any lower than that.
So maybe I can get a better interest rate you know if I were to live longer working my credit.
You know what if I tried getting a 4 percent interest rate let's try that.
Look at that.
Right.
But this is me manually going through the motions and adjusting this.
I'm going to undo these actions now again manually.
I don't want to do that.
I want Excel to figure this out for me.
This is called the goal seek.
So first that I'm in a select sell D-3.
This is the cell that I want to change I want to reach the goal of eleven hundred dollars a month.
So that's that's self-selected I'll get to my data to top the screen.
I'm going to go into that data tool section find what if analysis and I mean a grand goal seek.
All right.
Three little things.
First it wants to know quite well what do you want to set.
Right now I want to change the cell of D-3 I want to sell it something else why I want to sell it to
700.
That's what I can afford monthly right now.
It's going to allow us to change one of these three values that that formula the 69th uses.
So let's say I'm going to try changing the beat to sell or the mortgage amount to get me to the eleven
hundred dollars and an 8 percent interest rate.
OK.
And Excel is going to calculate it's iterating through all these values until it hits.
My goal and I can see for the next 300 months and an 8 percent interest rate I can afford one hundred
forty two thousand five hundred twenty one dollars worth of house right.
J.G. just a couple little things.
I want to set this sell to eleven hundred by changing this cell here and Excel does the rest.
Now if I hit OK it'll keep it if I hit cancel it will close it out and take me back to the default once
again.
Let's try this one more time.
I'll grab that cell beat D-3 backup data.
What if analysis and go seek.
Now this time I'm going to set the cell to eleven hundred.
But I'm going to try changing the interest rates.
You know what I want that $220000 house I got my eye on this house but I can't afford it.
So I need a lower interest rate.
OK.
Oh look at that.
I got two.
Eleven hundred.
But knocked off five percent right.
I got to work on my credit.
Right.
Or as we talked the bake or whatever but I got it could that that interest rate down from eight to three
percent.
Right.
Very slick tool rather than you manually going through and adjusting these numbers over and over again
trying to hit this goal get Excel to do it for you.
Try this out.
Open up the example file jumped to the goal seat tab and change that current payment from that 16:00
are roughly $79 down to eleven hundred or some other number by modify one of these values the mortgage
amount or the interest rate.
Try it out